package es.xpt.ysf.commons.location.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import es.xpt.ysf.commons.location.model.Province;
import es.xpt.ysf.configuration.Configuration;
import es.xpt.ysf.configuration.ConfigurationParameterNames;
import es.xpt.ysf.dao.Connection;
import es.xpt.ysf.dao.DAOErrors;
import es.xpt.ysf.dao.DAOException;
import es.xpt.ysf.dao.DbDAO;
import es.xpt.ysf.dao.PreparedStatement;
import es.xpt.ysf.dao.Statement;
import es.xpt.ysf.jaas.YSFPrincipal;

/**
 * Mantenimento de paises, provincias y localidades
 * @author: joseignacio.garces@xplayat.es
 * @version YSF 1.0
 * @since 
 */

/**
 * Province Data Access Object (DAO). This class contains all database handling
 * that is needed to permanently store and retrieve Province object instances.
 */

public class ProvinceDAO extends DbDAO<Province> {

	public ProvinceDAO(Connection c, YSFPrincipal principal, DAOErrors errors) {
		super(c, principal, errors);
	}

	public String getTableName() {
		return "ysf_cl_province";
	}


	/**
	 * findAll method will read all contents from database table and build a
	 * List containing valueObjects. Please note, that this method will consume
	 * huge amounts of resources if table has lot's of rows. This should only be
	 * used when target tables have only small amounts of data.
	 * 
	 * @return All data from the mapping table
	 * @exception DAOException
	 */
	public List<Province> findByCountry(String country, boolean used) throws DAOException {
		String selectStatement = "SELECT distinct c.id PRV_id, i.description PRV_description, '"
				+ principal.getLocale()
				+ "' PRV_locale, c.country PRV_country"
				+ " FROM ysf_cl_province c left outer join ysf_cl_province_i18n i"
				+ " on c.id=i.id and i.locale='" + principal.getLocale() + "'"
				+ ( used ? " inner join ysf_cl_address a on a.province=c.id " :"")
				+ " where c.country='" + country + "'"
				+ " ORDER BY description";

		PreparedStatement stmt = null;
		try {
			stmt = con.prepareStatement(selectStatement);
			return listQuery(stmt);
		} catch (SQLException se) {
			throw new DAOException("commons.location.provinceSearchError", se);
		} finally {
			if (stmt != null)
				stmt.close();
		}
	}

	
	public List<Province> getProvince(String id) throws DAOException {
		String selectStatement = "SELECT i.id PRV_id, i.description PRV_description, locale PRV_locale, c.country PRV_country"
				+ " FROM ysf_cl_province_i18n i, ysf_cl_province c"
				+ " where i.id='" + id + "' and c.id=i.id";
		return listQuery(selectStatement);
	}
	
	public Province getProvince(String id, String locale) throws DAOException {
		String selectStatement = "SELECT i.id PRV_id, i.description PRV_description, locale PRV_locale, c.country PRV_country"
				+ " FROM ysf_cl_province_i18n i, ysf_cl_province c"
				+ " where i.id='" + id + "' and c.id=i.id and i.locale='"+locale+"'";
		return singleQuery(selectStatement);
	}

	/**
	 * insert method will create new row in database according to supplied
	 * valueObject contents. Make sure that values for all NOT NULL columns are
	 * correctly specified. Also, if this table does not use automatic
	 * surrogate-keys the primary-key must be specified.
	 * 
	 * @param valueObject
	 *            This parameter contains the class instance to be created. If
	 *            automatic surrogate-keys are not used the Primary-key field
	 *            must be set for this to work properly.
	 * @exception DAOException
	 */
	public synchronized void insertProvince(Province valueObject)
			throws DAOException {

		String statement = "INSERT INTO ysf_cl_province" + " ( id, country)"
				+ " VALUES (?, ?)";
		PreparedStatement stmt = null;
		try {
			stmt = con.prepareStatement(statement);
			stmt.setObject(1, valueObject.getCode());
			stmt.setObject(2, valueObject.getCountryCode());
			int rowcount = stmt.executeUpdate();
			if (rowcount != 1) {
				throw new DAOException("commons.location.provinceInsertError");
			}
		} catch (SQLException se) {
			throw new DAOException("commons.location.provinceInsertError", se);
		} finally {
			if (stmt != null)
				stmt.close();
		}
	}

	public synchronized void insertProvincei18n(Province valueObject)
			throws DAOException {

		String statement = "INSERT INTO ysf_cl_province_i18n"
				+ " ( id, locale, description)" + " VALUES (?, ?, ?)";
		PreparedStatement stmt = null;
		try {
			stmt = con.prepareStatement(statement);
			stmt.setObject(1, valueObject.getCode());
			stmt.setObject(2, valueObject.getLocale());
			stmt.setObject(3, valueObject.getDescription());
			int rowcount = stmt.executeUpdate();
			if (rowcount != 1) {
				throw new DAOException("commons.location.provinceTranstlationInsertError");
			}
		} catch (SQLException se) {
			throw new DAOException("commons.location.provinceTranstlationInsertError", se);
		} finally {
			if (stmt != null)
				stmt.close();
		}
	}

	/**
	 * This method will remove the information from database as identified by by
	 * primary-key in supplied valueObject. If delete can not find matching row,
	 * SQLException will be thrown.
	 * 
	 * @param valueObject
	 *            This parameter contains the class instance to be deleted.
	 *            Primary-key field must be set for this to work properly.
	 * @exception DAOException
	 */
	public void delete(String id) throws DAOException {

		Statement stmt = null;

		try {
			stmt = con.createStatement();

			stmt.executeUpdate("DELETE FROM ysf_cl_province_i18n" + " WHERE id = '" + id + "'");

			stmt.executeUpdate("DELETE FROM ysf_cl_province" + " WHERE id = '" + id + "'");

		} catch (SQLException se) {
			throw new DAOException("commons.location.provinceDeleteError", se);
		} finally {
			if (stmt != null)
				stmt.close();
		}
	}
	

	
	/**
	 * This method will remove the information from database as identified by by
	 * primary-key in supplied valueObject. If delete can not find matching row,
	 * SQLException will be thrown.
	 * 
	 * @param valueObject
	 *            This parameter contains the class instance to be deleted.
	 *            Primary-key field must be set for this to work properly.
	 * @exception DAOException
	 */
	public void deletei18n(String id, String locale) throws DAOException {

		Statement stmt = null;

		try {
			stmt = con.createStatement();
			stmt.executeUpdate("DELETE FROM ysf_cl_province_i18n" + " WHERE id = '" + id + "' and locale = '" + locale + "'");

		} catch (SQLException se) {
			throw new DAOException("commons.location.provinceTraslationDeleteError", se);
		} finally {
			if (stmt != null)
				stmt.close();
		}
	}
	
	public void update(Province valueObject) throws DAOException {

		String selectStatement = "UPDATE ysf_cl_province_i18n SET description  = ? WHERE id = ? and locale=?";

		PreparedStatement stmt = null;
		try {
			stmt = con.prepareStatement(selectStatement);
			stmt.setString(1, valueObject.getDescription());
			stmt.setString(2, valueObject.getCode());
			stmt.setString(3, valueObject.getLocale());
			int rowcount = stmt.executeUpdate();
			if (rowcount == 0) {
				throw new DAOException("commons.location.provinceModifyError");
			}
			if (rowcount > 1) {
				throw new DAOException("commons.location.provinceModifyError");
			}
		} catch (SQLException se) {
			throw new DAOException("commons.location.provinceModifyError", se);
		} finally {
			if (stmt != null)
				stmt.close();
		}
	}

	public List<Province> getProvinceByZone(String zone) throws DAOException {
		String selectStatement = "SELECT i.id PRV_id, i.description PRV_description, locale PRV_locale, c.country PRV_country"
				+ " FROM ysf_cl_province_i18n i"
				+ " INNER JOIN ysf_cl_province c"
				+ " ON c.id = i.id AND i.locale='"+principal.getLocale().toString()+"'"
				+ " INNER JOIN tio_zone_location TZNL"
				+ " ON i.id = TZNL.province AND TZNL.city IS NULL AND TZNL.zone = '"+zone+"'";
		return listQuery(selectStatement);
	}
	

	protected Province populateBean(ResultSet rs) throws SQLException {
		return populateProvinceBean(rs);
	}

	private static Province populateProvinceBean(ResultSet rs) throws SQLException {
		return populateProvinceBean(rs,"PRV");
	}
	
	public static Province populateProvinceBean(ResultSet rs,String abbre) throws SQLException {
		Province dto = new Province();
		dto.setCode(rs.getString(abbre+"_id"));
		dto.setLocale(rs.getString(abbre+"_locale"));
		dto.setDescription(rs.getString(abbre+"_description"));
		dto.setCountryCode(rs.getString(abbre+"_country"));
		return dto;
	}

	public List<Province> suggestProvinces(String fragment) throws DAOException {
		String selectStatement = "SELECT i.id PRV_id, i.description PRV_description, p.country PRV_country, i.locale PRV_locale"
			+ " FROM ysf_cl_province_i18n i, ysf_cl_province p"
			+ " WHERE i.description LIKE '%" + fragment + "%' AND p.id=i.id";
		return listQuery(selectStatement);
	}
	
	public List<Province> findAllProvincesByCountry(String country) throws DAOException 
	{
		PreparedStatement stmt = null;
		try 
		{
			String queryStr = new Configuration(principal).getString(ConfigurationParameterNames.LOCATIONS_CONTAINER_PROVINCES);
			if(queryStr==null || queryStr.isEmpty())
				return findByCountry(country, false);
			
			stmt = con.prepareStatement(queryStr);
			stmt.setString(1, country);
			
			return listQueryAllProvinces(stmt);
		} catch (SQLException se) {
			throw new DAOException("commons.location.provinceSearchError", se);
		} finally {
			if (stmt != null)
				stmt.close();
		}
	}
	
	private List<Province> listQueryAllProvinces(PreparedStatement stmt) throws DAOException 
	{
		ArrayList<Province> searchResults = new ArrayList<Province>();
		ResultSet rs = null;
		try 
		{
			rs = stmt.executeQuery();
			while (rs.next()) 
			{
				Province dto = new Province();
				dto.setCode(rs.getString("id"));
				dto.setCountryCode(rs.getString("country"));
				dto.setDescription(rs.getString("description"));
				dto.setLocale("es_ES"); // FIXME: locale hardcode
				searchResults.add(dto);
			}
		} catch (SQLException se) {
		      throw new DAOException(se);
		} finally {
			if (rs != null)	rs = null; 
	    	if (stmt != null) stmt.close();
		}
		
		return searchResults;
	}
}